create table dbo.ReadMessages
(
	[TopicId] [int] NOT NULL,
	[MessageId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	constraint [UQ_ReadMessages] unique
	(
		[TopicId] ASC,
		[MessageId] ASC,
		[UserId] ASC
	)
)

GO

create procedure [dbo].[SPTopicsGetUnreadMessagesCount]
	@TopicId int
	,@ReaderId int
as
	set nocount on;

	select
		COUNT(*)
	from
		dbo.MessagesComplete M 
	left join dbo.ReadMessages R
		on M.MessageId=R.MessageId 
			and M.TopicId=R.TopicId
			and R.UserId = @ReaderId
	where
		M.TopicId = @TopicId
	and R.UserId is null

go

ALTER PROCEDURE [dbo].[SPMessagesGetByTopic]
	@TopicId int
	,@ReaderUserId int = null
	,@PageSize int
	,@PageNumber int
AS

	declare @startRow int, @endRow int;
	set @startRow = (@PageNumber - 1) * @PageSize + 1;
	set @endRow = @PageNumber * @PageSize;

	if @ReaderUserId is not null begin
			with pagedMessages as
			(
				select 
					M.TopicId
					,M.MessageId
					,R.UserId as ReadBy
					,ROW_NUMBER() OVER(ORDER BY M.TopicId, M.MessageId asc) as RowNumber
				from 
					dbo.MessagesComplete M
				left join dbo.ReadMessages R
					on M.MessageId=R.MessageId 
						and M.TopicId=R.TopicId
						and R.UserId = @ReaderUserId
				where 
					M.TopicId = @TopicId
			)
			insert into dbo.ReadMessages(TopicId, MessageId, UserId)
			select
				TopicId
				,MessageId
				,@ReaderUserId
			from
				pagedMessages
			where
				RowNumber between @startRow and @endRow
				and ReadBy is null
	end;
		

	with pagedMessages as
	(
		SELECT 
			ROW_NUMBER() OVER (ORDER BY M.TopicId, M.MessageId asc) AS RowNumber
			,M.TopicId
			,M.MessageId
			,M.MessageBody
			,M.MessageCreationDate
			,M.MessageLastEditDate
			,M.ParentId
			,M.UserId
			,M.UserName
			,M.UserSignature
			,M.UserGroupId
			,M.UserGroupName
			,M.UserPhoto
			,M.UserRegistrationDate
			,M.Active
			,COUNT(*) OVER() AS TotalMessages
		FROM 
			dbo.MessagesComplete M
		WHERE 
			M.TopicId = @TopicId
	)
	select
		TopicId
		,MessageId
		,MessageBody
		,MessageCreationDate
		,MessageLastEditDate
		,ParentId
		,UserId
		,UserName
		,UserSignature
		,UserGroupId
		,UserGroupName
		,UserPhoto
		,UserRegistrationDate
		,Active
		,TotalMessages
	from
		pagedMessages
	where
		RowNumber between @startRow and @endRow
go

ALTER PROCEDURE [dbo].[SPTopicsGetByForumLatest]
	@ForumId int = 2
	,@StartIndex int = 1
	,@Length int = 10
	,@UserGroupId int = null
AS
SELECT
	*
FROM
	(SELECT
		ROW_NUMBER()
			OVER 
				(ORDER BY TopicOrder desc
				 ,
				 (CASE 
					WHEN M.MessageCreationDate > T.TopicCreationDate THEN M.MessageCreationDate
					ELSE T.TopicCreationDate
				END) desc)
			AS RowNumber
		,T.TopicId
		,T.TopicTitle
		,T.TopicShortName
		,T.TopicDescription
		,T.TopicCreationDate
		,T.TopicViews
		,T.TopicReplies
		,T.UserId
		,T.TopicTags
		,T.TopicIsClose
		,T.TopicOrder
		,T.LastMessageId
		,T.UserName
		,M.MessageCreationDate
		,M.UserId AS MessageUserId
		,MU.UserName AS MessageUserName
		,T.ReadAccessGroupId
		,T.PostAccessGroupId
	FROM
		TopicsComplete T
		LEFT JOIN [Messages] M ON M.TopicId = T.TopicId AND M.MessageId = T.LastMessageId AND M.Active = 1
		LEFT JOIN Users MU ON MU.UserId = M.UserId
	WHERE
		T.ForumId = @ForumId
		AND
		ISNULL(T.ReadAccessGroupId,-1) <= ISNULL(@UserGroupId,-1)
	) T
WHERE
	RowNumber BETWEEN @StartIndex AND @StartIndex + @Length
go

ALTER PROCEDURE [dbo].[SPTopicsGetByForum]
	@ForumId int = 2
	,@StartIndex int = 0
	,@Length int = 10
	,@UserGroupId int = null
AS
SELECT
	*
FROM
	(SELECT
		ROW_NUMBER()
			OVER 
				(ORDER BY TopicOrder desc
				 ,TopicViews desc)
			AS RowNumber
		,T.TopicId
		,T.TopicTitle
		,T.TopicShortName
		,T.TopicDescription
		,T.TopicCreationDate
		,T.TopicViews
		,T.TopicReplies
		,T.UserId
		,T.TopicTags
		,T.TopicIsClose
		,T.TopicOrder
		,T.LastMessageId
		,T.UserName
		,M.MessageCreationDate
		,M.UserId AS MessageUserId
		,MU.UserName AS MessageUserName
		,T.ReadAccessGroupId
		,T.PostAccessGroupId
	FROM
		TopicsComplete T
		LEFT JOIN [Messages] M ON M.TopicId = T.TopicId AND M.MessageId = T.LastMessageId AND M.Active = 1
		LEFT JOIN Users MU ON MU.UserId = M.UserId
	WHERE
		T.ForumId = @ForumId
		AND
		ISNULL(T.ReadAccessGroupId,-1) <= ISNULL(@UserGroupId,-1)
	) T
WHERE
	RowNumber BETWEEN @StartIndex AND @StartIndex + @Length

go